import pandas as pd
from literature.models.Literature import Literature, Keyword
from literature.models.Institution import Institution
from literature.models.Author import Author
from literature.models.GlobalTables import Language, LiteratureStatusType, LiteratureType, JournalPublicationCycleType, InstitutionType, KeywordType
from literature.models.Publications import Journal, Publication
from dataset.models import *
from control.models.User import User
from django.core.exceptions import ObjectDoesNotExist
from django.db.utils import IntegrityError
from django.db import transaction
from datetime import datetime
from tqdm import tqdm
import re

def fix_liter_index():
    for obj in Literature.objects.all():
        if obj.liter_code:  # 跳过空值
            try:
                num = int(obj.liter_code)
                obj.liter_code = f"{num:07d}"  # 格式化为6位
                obj.save(update_fields=['liter_code'])  # 只更新该字段
            except ValueError:
                print(f"跳过非数字值: {obj.liter_code} (ID: {obj.id})")

def fix_dipper_liter():
    dataset = ExpDataset.objects.all().order_by("create_date")
    print(len(dataset))
    unit_file_path = "../dippr_tables/3.Const Properties Details-20250306R1.xlsx"
    sheet_name = "Sheet1"
    df1 = pd.read_excel(unit_file_path, sheet_name=sheet_name, header=2, dtype=str)
    selected_data1 = df1[['nPCdataCitID', 'Reference']].fillna("")
    print("df1 len is:", len(selected_data1))

    unit_file_path = "../dippr_tables/5.Tdep Data Sets-20250605R1.xlsx"
    sheet_name = "入库结构化表"
    df2 = pd.read_excel(unit_file_path, sheet_name=sheet_name, header=2, dtype=str)
    selected_data = df2[['nPCdataCitID', 'Ref']].fillna("")


    group_ref_list = []
    last_ref = ""
    for _, row in selected_data.iterrows():
        current_ref = row["Ref"]
        if current_ref != last_ref:
            group_ref_list.append(row["nPCdataCitID"])
            last_ref = current_ref
    print("df2 len is:", len(group_ref_list))
    group_ref_series = pd.Series(group_ref_list, name='nPCdataCitID')
    combined_series = pd.concat([selected_data1['nPCdataCitID'], group_ref_series], ignore_index=True)

    with transaction.atomic():
        for i, obj in enumerate(dataset):
            if i >= len(combined_series):
                break
            raw_code = combined_series.iloc[i]
            if pd.isna(raw_code) or raw_code == "":
                continue
            else:
                # 尝试格式化，失败则跳过

                liter_entity = Literature.objects.filter(liter_code=raw_code).first()
                if liter_entity:
                    obj.source_liter = liter_entity
                    obj.save(update_fields=['source_liter'])
                else:
                    continue
    updated_count = ExpDataset.objects.filter(source_liter__isnull=False).count()
    print(f"成功更新了 {updated_count} 条记录的 source_liter")
    print(f"combined_series 长度: {len(combined_series)}")
            
        
